﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data.Common;
using System.IO;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.Checksums;
using System.Xml;
using System.Globalization;
using ICSharpCode.SharpZipLib.Core;
using Yogesh.ExcelXml;
using System.Data;
//using System.Data.SQLite;

/// <summary>
/// Generates resources and outputs to various formats
/// </summary>
public static class ResourceGenerator {
    /// <summary>
    /// Creates Dictionaries to store bundles from the database.
    /// Return value format is: A KeyValuePair containing &lt;summary (or null if not needed), Dictionary&lt;bundlename, Dictionary&lt;language-code, Dictionary&lt;element, resource&gt;&gt;&gt;&gt;
    /// </summary>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="applicationId">The application ID.</param>
    /// <param name="threshold">The completion threshold.</param>
    /// <param name="includedBundles">The bundles to include.</param>
    /// <param name="includedLanguages">The language codes to include.</param>
    /// <param name="defaultLanguage">The default language.</param>
    /// <param name="includeSummary"><c>True</c> if a summary of the generated resources should be included.</param>
    /// <returns>A KeyValuePair containing &lt;summary (or null if not needed), Dictionary&lt;bundlename, Dictionary&lt;language-code, Dictionary&lt;element, resource&gt;&gt;&gt;&gt;</returns>
    public static KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>> GenerateBundles(string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary) {
        Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>> bundles = new Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>(); // <BundleName, <abbrev, translatedResources>>>
        List<string> errors = new List<string>();
        StringBuilder summary = null;
        if(includeSummary) {
            summary = new StringBuilder().AppendFormat("Resources generated by ResourceBlender.NET v{0} on {1}\n\n", Constants.Version.ToString(3), DateTime.Now.ToShortDateString());
        }

        Dictionary<string, int> completionMap = new Dictionary<string, int>();
        int totalStrings = 0;
        Dictionary<string, Dictionary<string, RBTranslation>> defaultLanguageStrings = new Dictionary<string, Dictionary<string, RBTranslation>>();
        Database db = new Database();
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = string.Format(@"SELECT language, bundle, elementname, translation, translatorcomment, b.name AS bundlename FROM {0}elements AS e INNER JOIN {0}resources AS r 
                ON r.resourceid = e.resourceid LEFT JOIN {0}bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language = @defaultLang {1}", db.TablePrefix, db.GenerateInClause("bundle", includedBundles, true));
            cmd.AddParameterWithValue("@applicationId", applicationId);
            cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
            DbDataReader reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for the default language
                string bundleName = reader["bundlename"].ToString(); // id of the bundle
                string elementName = reader["elementname"].ToString(); // eg: element
                if(!defaultLanguageStrings.ContainsKey(bundleName)) { //  first element in this form name
                    defaultLanguageStrings[bundleName] = new Dictionary<string, RBTranslation>();
                }
                defaultLanguageStrings[bundleName][elementName] = new RBTranslation(string.Empty, reader["translation"].ToString(), reader["translatorcomment"].ToString());
                totalStrings++;
            }
            reader.Close();
            if(includeSummary) {
                string fmt = Resources.UIStrings.AppElementBundleSummary ?? "Application '{0}' contains {1} {2} in {3} {4}";
                summary.AppendFormat(fmt + ":\n", applicationName, totalStrings, totalStrings == 1 ? Resources.UIStrings.Element : Resources.UIStrings.Elements, defaultLanguageStrings.Count, defaultLanguageStrings.Count == 1 ? Resources.UIStrings.Bundle : Resources.UIStrings.Bundles);
                foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> bundle in defaultLanguageStrings) {
                    summary.Append("*").AppendFormat("{0} ({1} {2})", bundle.Key, bundle.Value.Count, bundle.Value.Count == 1 ? Resources.UIStrings.Element : Resources.UIStrings.Elements).Append("\n");
                }
                summary.Append("\n").AppendFormat(Resources.UIStrings.OmittingLangsLessThanPc, threshold).Append("\n");
            }

            cmd.CommandText = string.Format(@"SELECT language, bundle, elementname, translation, translatorcomment, b.name AS bundlename FROM {0}elements AS e INNER JOIN {0}resources AS r 
                ON r.resourceid = e.resourceid LEFT JOIN {0}bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language != @defaultLang {1} {2}",
                db.TablePrefix, db.GenerateInClause("language", includedLanguages, true), db.GenerateInClause("bundle", includedBundles, true));
            reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                string abbrev = reader["language"].ToString();
                string bundle = reader["bundle"].ToString(); // id of the bundle
                string bundleName = reader["bundlename"].ToString(); // eg: bundle1
                string elementName = reader["elementname"].ToString(); // eg: element
                if(includedBundles.Contains(bundle)) {
                    if(!completionMap.ContainsKey(abbrev)) {
                        completionMap[abbrev] = 0;
                    }
                    completionMap[abbrev]++;
                    if(!bundles.ContainsKey(bundleName)) { //  first element in this bundle
                        bundles[bundleName] = new Dictionary<string, Dictionary<string, RBTranslation>>();
                    }
                    if(!bundles[bundleName].ContainsKey(abbrev)) {
                        bundles[bundleName][abbrev] = new Dictionary<string, RBTranslation>();
                    }
                    bundles[bundleName][abbrev][elementName] = new RBTranslation(string.Empty, reader["translation"].ToString(), reader["translatorcomment"].ToString());
                }
            }
            reader.Close();

            if(includedLanguages.Contains(defaultLanguage)) { // if the user chose to include the default language in the zip
                if(includeSummary) {
                    string defaultLanguageName = Utils.GetSafeEnglishName(defaultLanguage);
                    summary.Append("*").AppendFormat(Resources.UIStrings.IncludedLangCompletion, defaultLanguageName, defaultLanguage, "100").Append("\n");
                }
                foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> defaultBundle in defaultLanguageStrings) { // the default language is loaded separately from the rest, merge them here
                    if(!bundles.ContainsKey(defaultBundle.Key)) { //  first element in this bundle
                        bundles[defaultBundle.Key] = new Dictionary<string, Dictionary<string, RBTranslation>>();
                    }
                    if(!bundles[defaultBundle.Key].ContainsKey(defaultLanguage)) {
                        bundles[defaultBundle.Key][defaultLanguage] = new Dictionary<string, RBTranslation>();
                    }
                    foreach(KeyValuePair<string, RBTranslation> element in defaultBundle.Value) {
                        bundles[defaultBundle.Key][defaultLanguage][element.Key] = new RBTranslation(string.Empty, element.Value.Translation, element.Value.Comment);
                    }
                }
            }

            // remove languages below the completion threshold
            List<string> incompleteLanguages = new List<string>();
            foreach(KeyValuePair<string, int> languageCompletion in completionMap) { // key=abbrev, val=number of translatedStrings
                int completionPercentage = (int)Math.Round((languageCompletion.Value / (double)totalStrings) * 100);
                if(completionPercentage > threshold) {
                    if(includeSummary) {
                        string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                        summary.Append("*").AppendFormat(Resources.UIStrings.IncludedLangCompletion, languageName, languageCompletion.Key, completionPercentage).Append("\n");
                    }
                    foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> bundle in defaultLanguageStrings) { // key=bundlename, val=Dictionary<element,translation>
                        if(!bundles.ContainsKey(bundle.Key)) {
                            bundles[bundle.Key] = new Dictionary<string, Dictionary<string, RBTranslation>>();
                        }
                        if(!bundles[bundle.Key].ContainsKey(languageCompletion.Key)) {
                            bundles[bundle.Key].Add(languageCompletion.Key, bundle.Value);
                        } else {
                            foreach(KeyValuePair<string, RBTranslation> element in bundle.Value) {
                                if(!bundles[bundle.Key][languageCompletion.Key].ContainsKey(element.Key)) {
                                    bundles[bundle.Key][languageCompletion.Key].Add(element.Key, element.Value);
                                }
                            }
                        }
                    }
                } else {
                    if(includeSummary) {
                        string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                        summary.Append("*").AppendFormat(Resources.UIStrings.OmittedLangCompletion, languageName, languageCompletion.Key, completionPercentage).Append("\n");
                    }
                    foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, RBTranslation>>> form in bundles) {
                        form.Value.Remove(languageCompletion.Key);
                    }
                }
            }
            return new KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>>(includeSummary ? summary.ToString() : null, bundles);
        }
    }

    /// <summary>
    /// Outputs to Firefox .properties and .dtd files.
    /// </summary>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="applicationId">The application ID.</param>
    /// <param name="threshold">The completion threshold.</param>
    /// <param name="includedBundles">The bundles to include.</param>
    /// <param name="includedLanguages">The languages to include.</param>
    /// <param name="defaultLanguage">The default language.</param>
    /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
    public static void GenerateFirefox(string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary) {
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> resources = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <BundleName, <abbrev, <elementname, translation>>>
        List<string> errors = new List<string>();
        StringBuilder summary = null;
        if(includeSummary) {
            summary = new StringBuilder("Resources generated by ResourceBlender.NET v").Append(Constants.Version.ToString(3))
                                .Append(" on ").Append(DateTime.Now.ToShortDateString()).Append("\n");
        }

        Dictionary<string, int> completionMap = new Dictionary<string, int>();
        int totalStrings = 0;
        Dictionary<string, Dictionary<string, string>> defaultLanguageStrings = new Dictionary<string, Dictionary<string, string>>();
        Database db = new Database();   
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            StringBuilder sqlBuffer = new StringBuilder("SELECT language, bundle, elementname, translation, b.name AS bundlename FROM ");
            sqlBuffer.Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language = @defaultLang").Append(db.GenerateInClause("bundle", includedBundles, true));
            cmd.CommandText = sqlBuffer.ToString();
            cmd.AddParameterWithValue("@applicationId", applicationId);
            cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
            DbDataReader reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                string bundleName = reader["bundlename"].ToString(); // JavaScript / XUL
                string elementName = reader["elementname"].ToString(); // eg: element
                if(!defaultLanguageStrings.ContainsKey(bundleName)) { //  first element in this form name
                    defaultLanguageStrings[bundleName] = new Dictionary<string, string>();
                }
                defaultLanguageStrings[bundleName][elementName] = reader["translation"].ToString();
                totalStrings++;
            }
            reader.Close();
            if(includeSummary) {
                string fmt = Resources.UIStrings.AppElementBundleSummary ?? "Application '{0}' contains {1} {2} in {3} {4}";
                summary.AppendFormat(fmt + ":\n", applicationName, totalStrings, totalStrings == 1 ? Resources.UIStrings.Element : Resources.UIStrings.Elements, defaultLanguageStrings.Count, defaultLanguageStrings.Count == 1 ? Resources.UIStrings.Bundle : Resources.UIStrings.Bundles);
                foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) {
                    summary.Append("*").AppendFormat("{0} ({1} {2})", bundle.Key, bundle.Value.Count, bundle.Value.Count == 1 ? Resources.UIStrings.Element : Resources.UIStrings.Elements).Append("\n");
                }
                summary.Append("\n").AppendFormat(Resources.UIStrings.OmittingLangsLessThanPc, threshold).Append("\n");
            }

            sqlBuffer.Remove(0, sqlBuffer.Length);
            sqlBuffer.Append("SELECT language, bundle, elementname, translation, b.name AS bundlename FROM ").Append(db.TablePrefix).Append("elements AS e INNER JOIN ")
                     .Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language != @defaultLang")
                     .Append(db.GenerateInClause("language", includedLanguages, true)).Append(db.GenerateInClause("bundle", includedBundles, true));
            cmd.CommandText = sqlBuffer.ToString();
            reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                string abbrev = reader["language"].ToString();
                string bundle = reader["bundle"].ToString();
                string bundleName = reader["bundlename"].ToString(); // JavaScript / XUL
                string elementName = reader["elementname"].ToString(); // eg: element
                if(includedBundles.Contains(bundle) && includedLanguages.Contains(abbrev)) {
                    if(!completionMap.ContainsKey(abbrev)) {
                        completionMap[abbrev] = 0;
                    }
                    completionMap[abbrev]++;
                    if(!resources.ContainsKey(abbrev)) { //  first element in this form name
                        resources[abbrev] = new Dictionary<string, Dictionary<string, string>>();
                    }
                    if(!resources[abbrev].ContainsKey(bundleName)) {
                        resources[abbrev][bundleName] = new Dictionary<string, string>();
                    }
                    resources[abbrev][bundleName][elementName] = reader["translation"].ToString();
                }
            }
            reader.Close();

            if(includedLanguages.Contains(defaultLanguage)) {
                if(includeSummary) {
                    string defaultLanguageName = Utils.GetSafeEnglishName(defaultLanguage);
                    summary.Append("*").AppendFormat(Resources.UIStrings.IncludedLangCompletion, defaultLanguageName, defaultLanguage, "100").Append("\n");
                }
                resources[defaultLanguage] = new Dictionary<string, Dictionary<string, string>>();
                foreach(KeyValuePair<string, Dictionary<string, string>> defaultBundle in defaultLanguageStrings) {
                    resources[defaultLanguage][defaultBundle.Key] = new Dictionary<string, string>();
                    foreach(KeyValuePair<string, string> element in defaultBundle.Value) {
                        resources[defaultLanguage][defaultBundle.Key][element.Key] = element.Value;
                    }
                }
            }

            // remove languages below the completion threshold
            List<string> incompleteLanguages = new List<string>();
            foreach(KeyValuePair<string, int> languageCompletion in completionMap) { // key=abbrev, val=translatedStrings
                int completionPercentage = (int)Math.Round((languageCompletion.Value / (double)totalStrings) * 100);
                if(completionPercentage > threshold) {
                    if(includeSummary) {
                        string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                        summary.Append("*").AppendFormat(Resources.UIStrings.IncludedLangCompletion, languageName, languageCompletion.Key, completionPercentage).Append("\n");
                    }
                    if(!resources.ContainsKey(languageCompletion.Key)) { // there are no resources for this language, use the default language instead
                        resources[languageCompletion.Key] = defaultLanguageStrings;
                    } else {
                        foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) {
                            if(!resources[languageCompletion.Key].ContainsKey(bundle.Key)) { // some resources exist, but this bundle does not, use the default language for this bundle
                                resources[languageCompletion.Key][bundle.Key] = bundle.Value;
                            } else {
                                foreach(KeyValuePair<string, string> element in bundle.Value) {
                                    if(!resources[languageCompletion.Key][bundle.Key].ContainsKey(element.Key)) {
                                        resources[languageCompletion.Key][bundle.Key].Add(element.Key, element.Value);
                                    }
                                }
                            }
                        }
                    }
                } else {
                    if(includeSummary) {
                        string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                        summary.Append("*").AppendFormat(Resources.UIStrings.OmittedLangCompletion, languageName, languageCompletion.Key, completionPercentage).Append("\n");
                    }
                    resources.Remove(languageCompletion.Key);
                }
            }
        }

        MemoryStream ms = new MemoryStream();
        ZipOutputStream zipStream = new ZipOutputStream(ms);
        zipStream.SetLevel(9);
        Crc32 crc = new Crc32();
        StringBuilder resourceBuffer;

        foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> language in resources) {
            /* language.Key = language abbreviation
               language.Value = Dictionary<bundleName, Dictionary<element, resource>> */
            ZipEntry zipEntry = new ZipEntry(language.Key + "\\contents.rdf");
            string cultureName = Utils.GetSafeEnglishName(language.Key);
            resourceBuffer = new StringBuilder();
            resourceBuffer.Append(string.Format(Constants.ContentsRDF, language.Key, cultureName, applicationName));
            zipEntry.DateTime = DateTime.Now;
            byte[] buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
            zipEntry.Size = buffer.Length;

            zipStream.PutNextEntry(zipEntry);
            zipStream.Write(buffer, 0, buffer.Length);

            foreach(KeyValuePair<string, Dictionary<string, string>> bundle in language.Value) {
                /* bundle.Key = bundleName
                   bundle.Value = Dictionary<element, resource> */
                resourceBuffer = new StringBuilder();
                switch(bundle.Key.ToLower()) {
                    case "javascript":
                        zipEntry = new ZipEntry(language.Key + "\\" + Utils.SanitizeFilename(applicationName) + ".properties");
                        foreach(KeyValuePair<string, string> jsElement in bundle.Value) {
                            resourceBuffer.Append(jsElement.Key).Append("=").Append(jsElement.Value).Append("\n");
                        }
                        break;
                    case "xul":
                        zipEntry = new ZipEntry(language.Key + "\\" + Utils.SanitizeFilename(applicationName) + ".dtd");
                        foreach(KeyValuePair<string, string> xulElement in bundle.Value) {
                            resourceBuffer.Append("<!ENTITY ").Append(xulElement.Key).Append(" \"").Append(xulElement.Value).Append("\">\n");
                        }
                        break;
                }

                zipEntry.DateTime = DateTime.Now;
                buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                zipEntry.Size = buffer.Length;
                zipStream.PutNextEntry(zipEntry);
                zipStream.Write(buffer, 0, buffer.Length);
            }
        }

        if(includeSummary) {
            ZipEntry zipEntry = new ZipEntry("summary.txt");
            zipEntry.DateTime = DateTime.Now;
            byte[] summaryBytes = Encoding.UTF8.GetBytes(summary.ToString());
            zipEntry.Size = summaryBytes.Length;
            zipStream.PutNextEntry(zipEntry);
            zipStream.Write(summaryBytes, 0, summaryBytes.Length);
        }

        SendZip(ms, zipStream, applicationName);
    }

    /// <summary>
    /// Outputs to .NET resx files.
    /// </summary>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="applicationId">The application ID.</param>
    /// <param name="threshold">The completion threshold.</param>
    /// <param name="includedBundles">The bundles to include.</param>
    /// <param name="includedLanguages">The languages to include.</param>
    /// <param name="defaultLanguage">The default language.</param>
    /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
    /// <param name="compactFramework"><c>true</c> if the target platform is the Compact Framework, otherwise <c>false</c>.</param>
    public static void GenerateDotNet(string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary, bool compactFramework) {
        KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>> rawResources = GenerateBundles(applicationName, applicationId, threshold, includedBundles, includedLanguages, defaultLanguage, includeSummary);
        Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>> forms = rawResources.Value;

        MemoryStream ms = new MemoryStream();
        ZipOutputStream zipStream = new ZipOutputStream(ms);
        zipStream.SetLevel(9);
        Crc32 crc = new Crc32();

        foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, RBTranslation>>> form in forms) {
            /* form.Key = bundlename
               form.Value = Dictionary<abbrev, Dictionary<location, translation>> */
            foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> lang in form.Value) {
                /* lang.Key = abbrev
                   lang.Value = Dictionary<location, translation> */
                XmlDocument resx = new XmlDocument();
                resx.LoadXml(Constants.ResxSkeleton);
                XmlNode root = resx.SelectSingleNode("/root");

                string langAbbreviation = string.Empty;
                if(compactFramework) {
                    switch(lang.Key) {
                        case "zh-Hans": // buggy .NET Framework and contradictory MSDN articles
                            langAbbreviation = "zh-CHS";
                            break;
                        case "zh-Hant": // buggy .NET Framework and contradictory MSDN articles
                            langAbbreviation = "zh-CHT";
                            break;
                        default:
                            langAbbreviation = lang.Key;
                            break;
                    }
                } else {
                    langAbbreviation = lang.Key;
                }

                /*List<string> addedElements = new List<string>();
                String templateFilePath = HttpContext.Current.Server.MapPath(Path.Combine(Path.Combine("templates", applicationName), form.Key + "." + langAbbreviation + ".resx")); // try specific language first
                if(!File.Exists(templateFilePath)) { // just use the default
                    templateFilePath = HttpContext.Current.Server.MapPath(Path.Combine(Path.Combine("templates", applicationName), form.Key + ".resx"));
                }
                if(File.Exists(templateFilePath)) {
                    XmlDocument templateFile = new XmlDocument();
                    templateFile.Load(templateFilePath);
                    XmlNodeList existingValues = templateFile.SelectNodes("/root/data");
                    foreach(XmlNode node in existingValues) {
                        XmlNode newNode = resx.ImportNode(node, true);
                        root.AppendChild(newNode);
                        //TODO: check if the attribute doesn't exist - is exception thrown?
                        addedElements.Add(node.Attributes.GetNamedItem("name").InnerText);
                    }
                }*/
                foreach(KeyValuePair<string, RBTranslation> translatedResource in lang.Value) {
                    //                 if(!addedElements.Contains(translatedString.Key)) {
                    XmlNode nodeResourceData = resx.CreateElement("data");
                    nodeResourceData.Attributes.SetNamedItem(resx.CreateAttribute("name")).InnerText = translatedResource.Key;
                    nodeResourceData.AppendChild(resx.CreateElement("value")).InnerText = translatedResource.Value.Translation.Replace("\\n", "\n");
                    if(translatedResource.Value.Comment.Length > 0) {
                        nodeResourceData.AppendChild(resx.CreateElement("comment")).InnerText = translatedResource.Value.Comment.Replace("\\n", "\n");
                    }
                    root.AppendChild(nodeResourceData);
                    //           }
                }
                ZipEntry zipEntry;
                using(MemoryStream msEntry = new MemoryStream()) {
                    zipEntry = new ZipEntry(Utils.SanitizeFilename(form.Key + "." + langAbbreviation + ".resx"));
                    zipEntry.DateTime = DateTime.Now;
                    resx.Save(msEntry);
                    byte[] buffer = msEntry.ToArray();
                    msEntry.Close();
                    msEntry.Dispose();
                    zipEntry.Size = buffer.Length;

                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(buffer, 0, buffer.Length);

                    if(form.Key == "General" && lang.Key == defaultLanguage) { // todo: move to meta field in DB? universal fields?
                        zipEntry = new ZipEntry(Utils.SanitizeFilename(form.Key + ".resx"));
                        zipEntry.DateTime = DateTime.Now;
                        zipEntry.Size = buffer.Length;
                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(buffer, 0, buffer.Length);
                    }
                }
            }
        }

        if(includeSummary) {
            ZipEntry zipEntry = new ZipEntry("summary.txt");
            zipEntry.DateTime = DateTime.Now;
            byte[] summaryBytes = Encoding.UTF8.GetBytes(rawResources.Key.ToString());
            zipEntry.Size = summaryBytes.Length;
            zipStream.PutNextEntry(zipEntry);
            zipStream.Write(summaryBytes, 0, summaryBytes.Length);
        }

        SendZip(ms, zipStream, applicationName);
    }


    /// <summary>
    /// Outputs bundles to Java .properties files. This could be done using a template, but Java uses subtly different language codes
    /// </summary>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="applicationId">The application ID.</param>
    /// <param name="threshold">The completion threshold.</param>
    /// <param name="includedBundles">The bundles to include.</param>
    /// <param name="includedLanguages">The languages to include.</param>
    /// <param name="defaultLanguage">The default language.</param>
    /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
    /// <param name="escapeUnicode"><c>true</c> if code points above 127 (7F) should be replaced with their unicode escape sequences (\uXXXX).</param>
    public static void GenerateJava(string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary, bool escapeUnicode) {
        KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>> rawResources = GenerateBundles(applicationName, applicationId, threshold, includedBundles, includedLanguages, defaultLanguage, includeSummary);
        MemoryStream ms = new MemoryStream();
        ZipOutputStream zipStream = new ZipOutputStream(ms);
        zipStream.SetLevel(9);
        Crc32 crc = new Crc32();
        StringBuilder resourceBuffer;
        ZipEntry zipEntry;
        byte[] buffer;

        Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>> bundles = rawResources.Value;
        foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, RBTranslation>>> bundle in bundles) {
            /* bundle.Key = bundlename
               bundle.Value = Dictionary<abbrev, Dictionary<element, resource>> */
            foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> lang in bundle.Value) {
                /* lang.Key = abbrev
                   lang.Value = Dictionary<element, resource> */
                resourceBuffer = new StringBuilder();
                string langAbbreviation = Utils.DotNetCulture2JavaCulture(lang.Key);

                zipEntry = new ZipEntry(Utils.SanitizeFilename(bundle.Key + "_" + langAbbreviation + ".properties"));
                zipEntry.DateTime = DateTime.Now;
                foreach(KeyValuePair<string, RBTranslation> resource in lang.Value) {
                    resourceBuffer.Append(resource.Key).Append("=").AppendLine(escapeUnicode ? Utils.UnicodeEscape(resource.Value.Translation) : resource.Value.Translation);
                }
                buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                zipEntry.Size = buffer.Length;
                zipStream.PutNextEntry(zipEntry);
                zipStream.Write(buffer, 0, buffer.Length);
            }
        }

        if(rawResources.Key != null) {
            zipEntry = new ZipEntry("summary.txt");
            zipEntry.DateTime = DateTime.Now;
            byte[] summaryBytes = Encoding.UTF8.GetBytes(rawResources.Key);
            zipEntry.Size = summaryBytes.Length;
            zipStream.PutNextEntry(zipEntry);
            zipStream.Write(summaryBytes, 0, summaryBytes.Length);
        }

        SendZip(ms, zipStream, applicationName);
    }


    /// <summary>
    /// Generates resources using a custom template.
    /// </summary>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="applicationId">The application ID.</param>
    /// <param name="rawResources">The raw resources.</param>
    /// <param name="fileName">Name of each file.</param>
    /// <param name="fileStart">The text to prepend to the start of each file.</param>
    /// <param name="fileEnd">The text to append to the end of each file.</param>
    /// <param name="lineFormat">The line format.</param>
    public static void GenerateCustom(string applicationName, KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>> rawResources, string fileName, string fileStart, string fileEnd, string lineFormat) {
        MemoryStream ms = new MemoryStream();
        ZipOutputStream zipStream = new ZipOutputStream(ms);
        zipStream.SetLevel(9);
        Crc32 crc = new Crc32();
        StringBuilder resourceBuffer;
        ZipEntry zipEntry;
        byte[] buffer;

        string[] bundleTags = new string[] { "{=LanguageCode}",
                                                     "{=Bundle}" }, // valid only for the current bundle (filename, filestart, fileend)
                 elementTags = new string[] { "{=LanguageCode}",
                                                      "{=Bundle}",
                                                      "{=Element}",
                                                      "{=Translation}"
                                                       }, // valid for each element
                 replacementBundleTags = new string[] { "", "" },
                 replacementElementTags = new string[] { "", "", "", "" };
        Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>> bundles = rawResources.Value;
        foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, RBTranslation>>> bundle in bundles) {
            /* bundle.Key = bundlename
               bundle.Value = Dictionary<abbrev, Dictionary<element, translation>> */
            replacementBundleTags[1] = bundle.Key;
            replacementElementTags[1] = bundle.Key;
            foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> lang in bundle.Value) {
                /* lang.Key = abbrev
                   lang.Value = Dictionary<element, translation> */
                replacementBundleTags[0] = lang.Key;
                replacementElementTags[0] = lang.Key;
                resourceBuffer = new StringBuilder();
                if(fileStart.Length > 0) {
                    resourceBuffer.AppendLine(Utils.FormatTemplateTags(fileStart, bundleTags, replacementBundleTags));
                }
                zipEntry = new ZipEntry(Utils.SanitizeFilename(Utils.FormatTemplateTags(fileName, bundleTags, replacementBundleTags)));
                zipEntry.DateTime = DateTime.Now;
                foreach(KeyValuePair<string, RBTranslation> resource in lang.Value) {
                    replacementElementTags[2] = resource.Key;
                    replacementElementTags[3] = resource.Value.Translation;
                    resourceBuffer.AppendLine(Utils.FormatTemplateTags(lineFormat, elementTags, replacementElementTags));
                }
                if(fileEnd.Length > 0) {
                    resourceBuffer.AppendLine(Utils.FormatTemplateTags(fileEnd, bundleTags, replacementBundleTags));
                }
                buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                zipEntry.Size = buffer.Length;
                zipStream.PutNextEntry(zipEntry);
                zipStream.Write(buffer, 0, buffer.Length);
            }
        }

        string summary = rawResources.Key;
        if(summary != null) {
            zipEntry = new ZipEntry("summary.txt");
            zipEntry.DateTime = DateTime.Now;
            byte[] summaryBytes = Encoding.UTF8.GetBytes(summary);
            zipEntry.Size = summaryBytes.Length;
            zipStream.PutNextEntry(zipEntry);
            zipStream.Write(summaryBytes, 0, summaryBytes.Length);
        }

        SendZip(ms, zipStream, applicationName);
    }

    /// <summary>
    /// Outputs to GNU PO files.
    /// </summary>
    /// <param name="applicationName">Name of the application.</param>
    /// <param name="applicationId">The application ID.</param>
    /// <param name="threshold">The completion threshold.</param>
    /// <param name="includedBundles">The bundles to include.</param>
    /// <param name="languages">The language codes to include.</param>
    /// <param name="defaultLanguage">The default language.</param>
    /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
    public static void GeneratePO(string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary) {
        Dictionary<string, Dictionary<string, Dictionary<string, string>>> resources = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <BundleName, <abbrev, <elementname, translation>>>
        List<string> errors = new List<string>();
        StringBuilder summary = null;
        if(includeSummary) {
            summary = new StringBuilder("Resources generated by ResourceBlender.NET v").Append(Constants.Version.ToString(3))
                                .Append(" on ").Append(DateTime.Now.ToShortDateString()).Append("\n");
        }

        Dictionary<string, int> completionMap = new Dictionary<string, int>();
        int totalStrings = 0;
        Dictionary<string, Dictionary<string, POMetaData>> elementData = new Dictionary<string, Dictionary<string, POMetaData>>(); // <BundleName, <ElementName, Element meta-data>>
        Dictionary<string, Dictionary<string, string>> defaultLanguageStrings = new Dictionary<string, Dictionary<string, string>>(); // <BundleName, <ElementName, Untranslated string>>
        Database db = new Database();
        db.OpenConnection();
        using(Database.Command cmd = db.CreateCommand()) {
            StringBuilder sqlBuffer = new StringBuilder("SELECT language, bundle, elementname, translatorcomment, comment AS metadata, translation, b.name AS bundlename FROM ");
            sqlBuffer.Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language = @defaultLang").Append(db.GenerateInClause("bundle", includedBundles, true));
            cmd.CommandText = sqlBuffer.ToString();
            cmd.AddParameterWithValue("@applicationId", applicationId);
            cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
            DbDataReader reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for entries in the default language (untranslated strings)
                string bundleName = reader["bundlename"].ToString(),
                       elementName = reader["elementname"].ToString(),
                       rawMetaData = reader["metadata"].ToString(),
                       translation = reader["translation"].ToString(),
                       translatorComment = reader["translatorcomment"].ToString();
                if(!defaultLanguageStrings.ContainsKey(bundleName)) { //  first element in this bundle
                    defaultLanguageStrings[bundleName] = new Dictionary<string, string>();
                    elementData[bundleName] = new Dictionary<string, POMetaData>();
                }
                defaultLanguageStrings[bundleName][elementName] = translation;
                POMetaData data = POMetaData.Parse(rawMetaData);
                if(translatorComment.Length > 0) {
                    data.TranslatorsComments.Add(defaultLanguage, translatorComment);
                }
                data.UntranslatedString = translation;
                elementData[bundleName][elementName] = data;
                totalStrings++;
            }
            reader.Close();
            if(includeSummary) {
                string fmt = Resources.UIStrings.AppElementBundleSummary ?? "Application '{0}' contains {1} {2} in {3} {4}";
                summary.AppendFormat(fmt + ":\n", applicationName, totalStrings, totalStrings == 1 ? Resources.UIStrings.Element : Resources.UIStrings.Elements, defaultLanguageStrings.Count, defaultLanguageStrings.Count == 1 ? Resources.UIStrings.Bundle : Resources.UIStrings.Bundles);
                foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) {
                    summary.Append("*").AppendFormat("{0} ({1} {2})", bundle.Key, bundle.Value.Count, bundle.Value.Count == 1 ? Resources.UIStrings.Element : Resources.UIStrings.Elements).Append("\n");
                }
                summary.Append("\n").AppendFormat(Resources.UIStrings.OmittingLangsLessThanPc, threshold).Append("\n");
            }

            sqlBuffer.Remove(0, sqlBuffer.Length);
            sqlBuffer.Append("SELECT language, bundle, elementname, translation, translatorcomment, b.name AS bundlename FROM ").Append(db.TablePrefix).Append("elements AS e INNER JOIN ")
                     .Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language != @defaultLang")
                     .Append(db.GenerateInClause("language", includedLanguages, true)).Append(db.GenerateInClause("bundle", includedBundles, true));
            cmd.CommandText = sqlBuffer.ToString();
            reader = cmd.ExecuteReader();
            while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                string abbrev = reader["language"].ToString();
                string bundle = reader["bundle"].ToString();
                string bundleName = reader["bundlename"].ToString();
                string elementName = reader["elementname"].ToString(); // eg: element
                string translatorComment = reader["translatorcomment"].ToString();
                if(!defaultLanguageStrings.ContainsKey(bundleName) || !defaultLanguageStrings[bundleName].ContainsKey(elementName)) {
                    continue; // the default language is missing, so just skip this element
                }
                if(includedBundles.Contains(bundle) && includedLanguages.Contains(abbrev)) {
                    if(!completionMap.ContainsKey(abbrev)) {
                        completionMap[abbrev] = 0;
                    }
                    completionMap[abbrev]++;
                    if(!resources.ContainsKey(abbrev)) { // first element in this bundle
                        resources[abbrev] = new Dictionary<string, Dictionary<string, string>>();
                    }
                    if(!resources[abbrev].ContainsKey(bundleName)) {
                        resources[abbrev][bundleName] = new Dictionary<string, string>();
                    }
                    resources[abbrev][bundleName][elementName] = reader["translation"].ToString();
                    if(translatorComment.Length > 0) {
                        elementData[bundleName][elementName].TranslatorsComments.Add(abbrev, translatorComment);
                    }
                }
            }
            reader.Close();

            if(includedLanguages.Contains(defaultLanguage)) {
                if(includeSummary) {
                    string defaultLanguageName = Utils.GetSafeEnglishName(defaultLanguage);
                    summary.Append("*").AppendFormat(Resources.UIStrings.IncludedLangCompletion, defaultLanguageName, defaultLanguage, "100").Append("\n");
                }
                resources[defaultLanguage] = new Dictionary<string, Dictionary<string, string>>();
                foreach(KeyValuePair<string, Dictionary<string, string>> defaultBundle in defaultLanguageStrings) {
                    resources[defaultLanguage][defaultBundle.Key] = new Dictionary<string, string>();
                    foreach(KeyValuePair<string, string> element in defaultBundle.Value) {
                        resources[defaultLanguage][defaultBundle.Key][element.Key] = element.Value;
                    }
                }
            }

            // remove languages below the completion threshold
            List<string> incompleteLanguages = new List<string>();
            foreach(KeyValuePair<string, int> languageCompletion in completionMap) { // key=abbrev, val=translatedStrings
                int completionPercentage = (int)Math.Round((languageCompletion.Value / (double)totalStrings) * 100);
                if(completionPercentage > threshold) {
                    if(includeSummary) {
                        string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                        summary.Append("*").AppendFormat(Resources.UIStrings.IncludedLangCompletion, languageName, languageCompletion.Key, completionPercentage > 100 ? 100 : completionPercentage).Append("\n"); // default language unknown, could be > 100
                    }
                    if(!resources.ContainsKey(languageCompletion.Key)) { // there are no resources for this language, use the default language instead
                        resources[languageCompletion.Key] = resources[defaultLanguage];
                    } else {
                        foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) { // <BundleName, <ElementName, Meta-Data>>
                            if(!resources[languageCompletion.Key].ContainsKey(bundle.Key)) { // some resources exist, but this bundle does not, use the default language for this bundle
                                resources[languageCompletion.Key][bundle.Key] = defaultLanguageStrings[bundle.Key];
                            } else {
                                foreach(KeyValuePair<string, string> element in bundle.Value) {
                                    if(!resources[languageCompletion.Key][bundle.Key].ContainsKey(element.Key)) {
                                        resources[languageCompletion.Key][bundle.Key].Add(element.Key, element.Value);
                                    }
                                }
                            }
                        }
                    }
                } else {
                    if(includeSummary) {
                        string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                        summary.Append("*").AppendFormat(Resources.UIStrings.OmittedLangCompletion, languageName, languageCompletion.Key, completionPercentage > 100 ? 100 : completionPercentage).Append("\n"); // default language unknown, could be > 100
                    }
                    resources.Remove(languageCompletion.Key);
                }
            }
        }

        MemoryStream ms = new MemoryStream();
        ZipOutputStream zipStream = new ZipOutputStream(ms);
        zipStream.SetLevel(9);
        Crc32 crc = new Crc32();
        StringBuilder resourceBuffer = new StringBuilder(); ;

        foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> language in resources) {
            /* language.Key = language abbreviation
               language.Value = Dictionary<bundleName, Dictionary<element, resource>> */
            ZipEntry zipEntry;
            byte[] buffer;
            foreach(KeyValuePair<string, Dictionary<string, string>> bundle in language.Value) {
                /* bundle.Key = bundleName
                   bundle.Value = Dictionary<element, resource> */
                resourceBuffer.Remove(0, resourceBuffer.Length);
                zipEntry = new ZipEntry(Utils.SanitizeFilename(bundle.Key) + "\\" + Utils.SanitizeFilename(language.Key) + ".po");
                foreach(KeyValuePair<string, string> element in bundle.Value) {
                    POMetaData metaData = elementData[bundle.Key][element.Key];
                    if(resourceBuffer.Length > 0) { // omit whitespace at start of file
                        resourceBuffer.Append("\n\n");
                    }
                    if(metaData.TranslatorsComments.ContainsKey(language.Key)) {
                        string translatorComments = metaData.TranslatorsComments[language.Key];
                        if(translatorComments.Length > 0) {
                            foreach(string commentLine in translatorComments.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries)) {
                                resourceBuffer.Append("# ").Append(commentLine).Append("\n");
                            }
                        }
                    }
                    if(metaData.ExtractedComments.Count > 0) {
                        foreach(string comment in metaData.ExtractedComments) {
                            resourceBuffer.Append("#. ").Append(comment).Append("\n");
                        }
                    }
                    if(metaData.References.Count > 0) {
                        foreach(string reference in metaData.References) {
                            resourceBuffer.Append("#: ").Append(reference).Append("\n");
                        }
                    }
                    if(metaData.Flags.Count > 0) {
                        foreach(string flag in metaData.Flags) {
                            resourceBuffer.Append("#, ").Append(flag).Append("\n");
                        }
                    }
                    if(metaData.Context != null) {
                        resourceBuffer.Append("msgctxt \"").Append(metaData.Context).Append("\"\n");
                    }
                    string msgId = element.Key == "Generator" ? "" : metaData.UntranslatedString.Replace("\\n", "\\n\"\n\"");
                    if(msgId.EndsWith("\n\"")) {
                        msgId = msgId.Substring(0, msgId.Length - 2);
                    }
                    string msgStr = element.Value.Contains("\\n") ? element.Value.Replace("\\n", "\\n\"\n\"") : element.Value; // if it's a multiline string, replace linefeeds with \n"<newline>"
                    if(msgStr.EndsWith("\n\"")) {
                        msgStr = msgStr.Substring(0, msgStr.Length - 3);
                    }
                    resourceBuffer.AppendFormat("msgid \"{0}\"\nmsgstr \"{1}\"", msgId, msgStr);
                }

                zipEntry.DateTime = DateTime.Now;
                buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                zipEntry.Size = buffer.Length;
                zipStream.PutNextEntry(zipEntry);
                zipStream.Write(buffer, 0, buffer.Length);
            }
        }

        if(includeSummary) {
            ZipEntry zipEntry = new ZipEntry("summary.txt");
            zipEntry.DateTime = DateTime.Now;
            byte[] summaryBytes = Encoding.UTF8.GetBytes(summary.ToString());
            zipEntry.Size = summaryBytes.Length;
            zipStream.PutNextEntry(zipEntry);
            zipStream.Write(summaryBytes, 0, summaryBytes.Length);
        }

        SendZip(ms, zipStream, applicationName);
    }

    /// <summary>
    /// Exports to ResourceBlender XML format.
    /// </summary>
    /// <param name="log">The delegate to use to log status messages.</param>
    /// <param name="file">The file to which to export.</param>
    /// <param name="zipOutput"><c>true</c> if the output should be zipped</param>
    /// <param name="includeApps"><c>true</c> if applications should be included</param>
    /// <param name="includeElements"><c>true</c> if elements should be included</param>
    /// <param name="includeResources"><c>true</c> if resources should be included</param>
    /// <param name="includeTemplates"><c>true</c> if templates should be included</param>
    /// <param name="includeUsers"><c>true</c> if users should be included</param>
    public static void GenerateXML(bool zipOutput, bool includeApps, bool includeElements, bool includeResources, bool includeTemplates, bool includeUsers) {
        XmlDataDocument doc = new XmlDataDocument();
        XmlDeclaration decl = (XmlDeclaration)doc.CreateNode(XmlNodeType.XmlDeclaration, "", "");
        decl.Encoding = "UTF-8";
        doc.AppendChild(decl);
        XmlNode root = doc.CreateElement("resourceblender");
        doc.AppendChild(root);

        XmlElement ndMeta = doc.CreateElement("meta");
        root.AppendChild(ndMeta);
        XmlElement ndGenerator = doc.CreateElement("generator");
        ndGenerator.InnerText = "ResourceBlender.NET " + Constants.Version.ToString(3);
        ndMeta.AppendChild(ndGenerator);
        XmlElement ndGenerationDate = doc.CreateElement("generated");
        ndGenerationDate.InnerText = DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss G\\MT");
        ndMeta.AppendChild(ndGenerationDate);

        Database db = new Database();
        db.OpenConnection();
        if(includeApps) {
            using(Database.Command cmd = db.CreateCommand()) {
                XmlElement ndApplications = doc.CreateElement("applications");
                root.AppendChild(ndApplications);
                cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "applications";
                DbDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    XmlElement eleApplication = doc.CreateElement("application");
                    ndApplications.AppendChild(eleApplication);

                    XmlElement eleId = doc.CreateElement("id");
                    eleId.InnerText = HttpUtility.HtmlEncode(reader["id"].ToString());
                    eleApplication.AppendChild(eleId);

                    XmlElement eleName = doc.CreateElement("name");
                    eleName.InnerText = Utils.HtmlEncode(reader["name"].ToString());
                    eleApplication.AppendChild(eleName);

                    XmlElement eleDisplayname = doc.CreateElement("displayname");
                    eleDisplayname.InnerText = Utils.HtmlEncode(reader["displayname"].ToString());
                    eleApplication.AppendChild(eleDisplayname);

                    XmlElement eleDefaultLanguage = doc.CreateElement("defaultlanguage");
                    eleDefaultLanguage.InnerText = HttpUtility.HtmlEncode(reader["defaultlanguage"].ToString());
                    eleApplication.AppendChild(eleDefaultLanguage);

                    XmlElement eleLanguages = doc.CreateElement("languages");
                    eleLanguages.InnerText = HttpUtility.HtmlEncode(reader["languages"].ToString());
                    eleApplication.AppendChild(eleLanguages);
                }
                reader.Close();
                XmlElement ndBundles = doc.CreateElement("bundles");
                root.AppendChild(ndBundles);
                cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "bundles";
                reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    XmlElement eleBundle = doc.CreateElement("bundle");
                    ndBundles.AppendChild(eleBundle);

                    XmlElement eleId = doc.CreateElement("id");
                    eleId.InnerText = HttpUtility.HtmlEncode(reader["id"].ToString());
                    eleBundle.AppendChild(eleId);

                    XmlElement eleName = doc.CreateElement("name");
                    eleName.InnerText = Utils.HtmlEncode(reader["name"].ToString());
                    eleBundle.AppendChild(eleName);

                    XmlElement eleApplicationId = doc.CreateElement("application");
                    eleApplicationId.InnerText = Utils.HtmlEncode(reader["application"].ToString());
                    eleBundle.AppendChild(eleApplicationId);
                }
                reader.Close();
            }
        }
        if(includeElements) {
            using(Database.Command cmd = db.CreateCommand()) {
                XmlElement ndElements = doc.CreateElement("elements");
                root.AppendChild(ndElements);
                cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "elements";
                DbDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    XmlElement eleElement = doc.CreateElement("element");
                    ndElements.AppendChild(eleElement);

                    XmlElement eleApplicationId = doc.CreateElement("applicationid");
                    eleApplicationId.InnerText = HttpUtility.HtmlEncode(reader["applicationid"].ToString());
                    eleElement.AppendChild(eleApplicationId);

                    XmlElement eleBundleName = doc.CreateElement("bundle");
                    eleBundleName.InnerText = Utils.HtmlEncode(reader["bundle"].ToString());
                    eleElement.AppendChild(eleBundleName);

                    XmlElement eleElementName = doc.CreateElement("elementname");
                    eleElementName.InnerText = Utils.HtmlEncode(reader["elementname"].ToString());
                    eleElement.AppendChild(eleElementName);

                    XmlElement eleResourceId = doc.CreateElement("resourceid");
                    eleResourceId.InnerText = reader["resourceid"].ToString();
                    eleElement.AppendChild(eleResourceId);

                    XmlElement eleComment = doc.CreateElement("comment");
                    eleComment.InnerText = Utils.HtmlEncode(reader["comment"].ToString());
                    eleElement.AppendChild(eleComment);
                }
                reader.Close();
            }
        }
        if(includeResources) {
            using(Database.Command cmd = db.CreateCommand()) {
                XmlElement ndResources = doc.CreateElement("resources");
                root.AppendChild(ndResources);
                cmd.CommandText = "SELECT r.resourceid AS resourceid, language, translation, translatorcomment, comment FROM " + db.TablePrefix + "resources AS r LEFT JOIN " + db.TablePrefix + "resourcecomments AS c ON r.resourceid = c.resourceid ORDER BY r.resourceid ASC";
                DbDataReader reader = cmd.ExecuteReader();
                string lastResourceId = string.Empty,
                       currentResourceId = string.Empty;
                XmlElement eleResource = null;
                while(reader.Read()) {
                    currentResourceId = reader["resourceid"].ToString();
                    if(currentResourceId != lastResourceId) {
                        eleResource = doc.CreateElement("resource");
                        ndResources.AppendChild(eleResource);

                        XmlElement eleId = doc.CreateElement("id");
                        eleId.InnerText = HttpUtility.HtmlEncode(currentResourceId);
                        eleResource.AppendChild(eleId);

                        XmlElement eleComment = doc.CreateElement("comment");
                        eleComment.InnerText = Utils.HtmlEncode(reader["comment"].ToString());
                        eleResource.AppendChild(eleComment);

                        XmlElement eleLanguages = doc.CreateElement("languages");
                        eleResource.AppendChild(eleLanguages);
                    }
                    lastResourceId = currentResourceId;
                    XmlElement eleTranslatedLanguage = doc.CreateElement("language");
                    eleTranslatedLanguage.InnerText = Utils.HtmlEncode(reader["translation"].ToString());
                    eleResource.LastChild.AppendChild(eleTranslatedLanguage);

                    XmlAttribute attrAbbreviation = doc.CreateAttribute("code");
                    attrAbbreviation.InnerText = reader["language"].ToString();
                    eleTranslatedLanguage.Attributes.Append(attrAbbreviation);

                    string translatorComment = reader["translatorcomment"].ToString();
                    if(translatorComment.Length > 0) {
                        XmlAttribute attrTranslatorComment = doc.CreateAttribute("translatorcomment");
                        attrTranslatorComment.InnerText = Utils.HtmlEncode(translatorComment);
                        eleTranslatedLanguage.Attributes.Append(attrTranslatorComment);
                    }
                }
                reader.Close();
            }
        }
        if(includeTemplates) {
            using(Database.Command cmd = db.CreateCommand()) {
                XmlElement ndElements = doc.CreateElement("templates");
                root.AppendChild(ndElements);
                cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "templates";
                DbDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    XmlElement eleElement = doc.CreateElement("template");
                    ndElements.AppendChild(eleElement);

                    XmlElement eleTemplateName = doc.CreateElement("name");
                    eleTemplateName.InnerText = Utils.HtmlEncode(reader["name"].ToString());
                    eleElement.AppendChild(eleTemplateName);

                    XmlElement eleFileName = doc.CreateElement("filename");
                    eleFileName.InnerText = Utils.HtmlEncode(reader["filename"].ToString());
                    eleElement.AppendChild(eleFileName);

                    XmlElement eleFileStart = doc.CreateElement("filestart");
                    eleFileStart.InnerText = Utils.HtmlEncode(reader["filestart"].ToString());
                    eleElement.AppendChild(eleFileStart);

                    XmlElement eleFileEnd = doc.CreateElement("fileend");
                    eleFileEnd.InnerText = Utils.HtmlEncode(reader["fileend"].ToString());
                    eleElement.AppendChild(eleFileEnd);

                    XmlElement eleLine = doc.CreateElement("line");
                    eleLine.InnerText = Utils.HtmlEncode(reader["line"].ToString());
                    eleElement.AppendChild(eleLine);
                }
                reader.Close();
            }
        }
        if(includeUsers) {
            using(Database.Command cmd = db.CreateCommand()) {
                XmlElement ndUsers = doc.CreateElement("users");
                root.AppendChild(ndUsers);
                cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "users";
                DbDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    XmlElement eleUser = doc.CreateElement("user");
                    ndUsers.AppendChild(eleUser);

                    XmlElement eleId = doc.CreateElement("id");
                    eleId.InnerText = HttpUtility.HtmlEncode(reader["id"].ToString());
                    eleUser.AppendChild(eleId);

                    XmlElement eleUsername = doc.CreateElement("username");
                    eleUsername.InnerText = Utils.HtmlEncode(reader["username"].ToString());
                    eleUser.AppendChild(eleUsername);

                    XmlElement elePassword = doc.CreateElement("password");
                    elePassword.InnerText = Utils.HtmlEncode(reader["password"].ToString());
                    eleUser.AppendChild(elePassword);

                    XmlElement eleEmail = doc.CreateElement("email");
                    eleEmail.InnerText = Utils.HtmlEncode(reader["email"].ToString());
                    eleUser.AppendChild(eleEmail);

                    XmlElement elePermissions = doc.CreateElement("permissions");
                    elePermissions.InnerText = HttpUtility.HtmlEncode(reader["permissions"].ToString());
                    eleUser.AppendChild(elePermissions);

                    XmlElement eleAllowedProjects = doc.CreateElement("allowedprojects");
                    eleAllowedProjects.InnerText = Utils.HtmlEncode(reader["allowedprojects"].ToString());
                    eleUser.AppendChild(eleAllowedProjects);
                }
                reader.Close();
            }
        }

        using(MemoryStream msDoc = new MemoryStream())
        using(MemoryStream msZip = new MemoryStream()) {
            ZipOutputStream zipStream = new ZipOutputStream(msZip);
            zipStream.SetLevel(9);

            doc.Save(msDoc);
            byte[] buffer = msDoc.ToArray();
            msDoc.Close();
            ZipEntry zipEntry = new ZipEntry("ResourceBlender" + DateTime.Now.ToString("yyyy-MM-dd-HHmm") + ".xml");
            zipEntry.DateTime = DateTime.Now;
            zipEntry.Size = buffer.Length;
            zipStream.PutNextEntry(zipEntry);
            zipStream.Write(buffer, 0, buffer.Length);
            zipStream.Finish();

            SendZip(msZip, zipStream, "ResourceBlender");
        }
    }

    /// <summary>
    /// Exports resources to an XML spreadsheet
    /// </summary>
    /// <param name="applicationIds">The IDs of the applications to export.</param>
    /// <param name="selectedLanguages">The IDs of the languages to export.</param>
    /// <param name="selectedBundles">The IDs of the bundles to export.</param>
    /// <param name="zipOutput"><c>true</c> if the spreadsheet should be zipped, otherwise <c>false</c>.</param>
    public static void ExportToExcel(string[] applicationIds, string[] selectedLanguages, string[] selectedBundles, bool zipOutput) {
        Database db = null;
        Exception error = null;
        try {
            db = new Database();
            db.OpenConnection();
            ExcelXmlWorkbook wb = new ExcelXmlWorkbook();
            List<int> addedIds = new List<int>();
            wb.Properties.Title = "ResourceBlender Resources (" + DateTime.Now.ToString("yyyy-MM-dd-HHmm") + ")";
            int numExported = 0;
            using(Database.Command cmdApplication = db.CreateCommand())
            using(Database.Command cmdResources = db.CreateCommand()) {
                cmdApplication.CommandText = string.Format("SELECT displayname, defaultLanguage, languages FROM {0}applications WHERE id = @p", db.TablePrefix);
                cmdApplication.AddParameterWithValue("@p", "");
                cmdResources.AddParameterWithValue("@p", "");
                string applicationId = null,
                       applicationName = null,
                       defaultLanguage = null;
                string[] allLanguagesInApplication = null;
                int startingColumn = 1;
                List<string> selectedLanguagesInApplication = new List<string>(); // if just one list of languages is used, there are gaps for columns where a language from another application is not present in the current application because each app is on a separate sheet
                XmlStyle greenBackground = new XmlStyle(),
                         greyBackground = new XmlStyle();
                greenBackground.Interior.Color = System.Drawing.Color.FromArgb(234, 241, 221);
                greenBackground.Interior.Pattern = Pattern.Solid;
                greyBackground.Interior.Color = System.Drawing.Color.FromArgb(240, 240, 240);
                greyBackground.Interior.Pattern = Pattern.Solid;
                greyBackground.Font.Color = System.Drawing.Color.FromArgb(130, 130, 130);
                for(int i = 0; i < applicationIds.Length; i++) {
                    selectedLanguagesInApplication.Clear();
                    applicationId = applicationIds[i];
                    cmdApplication.Parameters["@p"].Value = applicationId;
                    cmdResources.Parameters["@p"].Value = applicationId;
                    using(DbDataReader reader = cmdApplication.ExecuteReader(CommandBehavior.SingleResult)) {
                        while(reader.Read()) {
                            applicationName = reader.GetString(0);
                            defaultLanguage = reader.GetString(1);
                            allLanguagesInApplication = reader.GetString(2).Trim().Split(',');
                        }
                        reader.Close();
                    }
                    if(applicationName == null || applicationName.Length == 0) {
                        throw new ArgumentNullException(string.Format(Resources.Errors.AppNotFoundName, ""));
                    }
                    selectedLanguagesInApplication.Add(defaultLanguage);

                    int rowIndex = 1;
                    Worksheet sheet = wb[i];
                    sheet.Name = applicationName;
                    Row row = sheet.AddRow();
                    int column = startingColumn + 1;
                    sheet[startingColumn, 0].Value = "Resource ID";
                    Cell headerCell = sheet[column++, 0];
                    headerCell.Value = defaultLanguage;
                    foreach(string lang in allLanguagesInApplication) {
                        if(selectedLanguages.Contains(lang)) {
                            if(lang != defaultLanguage) {
                                selectedLanguagesInApplication.Add(lang);
                                headerCell = sheet[column++, 0];
                                headerCell.Value = lang;
                            }
                        }
                    }
                    sheet.FreezeTopRows = 1;
                    if(selectedLanguagesInApplication.Count == 0) {
                        throw new ArgumentException(string.Format(Resources.Errors.NoLangsFoundAppName, applicationName));
                    }

                    //sheet.Table.Columns[startingColumn].Width = 200;
                    cmdResources.CommandText = string.Format(@"SELECT DISTINCT e.resourceid, e.id, r.language, r.translation, r.translatorcomment, c.comment FROM {0}elements AS e 
                                                               INNER JOIN {0}resources AS r ON r.resourceid = e.resourceid LEFT JOIN {0}resourcecomments AS c ON c.resourceid = r.resourceid 
                                                               WHERE applicationid = @p AND language IN('{1}') AND bundle IN('{2}') ORDER BY e.id",
                                                      db.TablePrefix, Utils.Implode(selectedLanguagesInApplication, "','", false), Utils.Implode(selectedBundles, "','", false));
                    using(DbDataReader reader = cmdResources.ExecuteReader()) {
                        int lastIdRead = -1;
                        while(reader.Read()) {
                            try {
                                int resourceId = int.Parse(reader["resourceid"].ToString());
                                Cell cell = null;
                                if(lastIdRead == -1) {
                                    if(addedIds.Contains(resourceId)) {
                                        continue;
                                    }
                                    addedIds.Add(resourceId);
                                    lastIdRead = resourceId;
                                    cell = sheet[startingColumn, rowIndex];
                                    cell.Value = resourceId;
                                    cell.Comment = reader["comment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                                    cell.Style = greyBackground;
                                    numExported++;
                                } else if(lastIdRead != resourceId) {
                                    if(addedIds.Contains(resourceId)) {
                                        continue;
                                    }
                                    addedIds.Add(resourceId);
                                    lastIdRead = resourceId;
                                    cell = sheet[startingColumn, ++rowIndex];
                                    cell.Value = resourceId;
                                    cell.Comment = reader["comment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                                    cell.Style = greyBackground;
                                    numExported++;
                                }
                                cell = sheet[selectedLanguagesInApplication.IndexOf(reader["language"].ToString()) + startingColumn + 1, rowIndex];
                                cell.Value = reader["translation"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                                cell.Comment = reader["translatorcomment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                            } catch(Exception ex) {
                                System.Diagnostics.Debug.WriteLine(string.Format("Error saving resource: {0}", ex.Message));
                            }
                        }
                        reader.Close();
                    }
                    Range defaultColumn = new Range(sheet[startingColumn + 1, 0], sheet[startingColumn + 1, rowIndex]);
                    defaultColumn.Style = greenBackground;
                    Range headerCells = new Range(sheet[startingColumn, 0], sheet[column, 0]);
                    headerCells.Font.Bold = true;
                }
            }
            HttpResponse res = HttpContext.Current.Response;
            using(MemoryStream msXml = new MemoryStream()) { // holding stream for workbook
                string fileName = "ResourceBlender" + DateTime.Now.ToString("yyyy-MM-dd-HHmm");
                if(zipOutput) {
                    using(MemoryStream msZip = new MemoryStream()) { // output stream for zip
                        ZipOutputStream zipStream = new ZipOutputStream(msZip);
                        zipStream.SetLevel(9);

                        wb.Export(msXml);
                        byte[] buffer = msXml.ToArray();
                        msXml.Close();
                        ZipEntry zipEntry = new ZipEntry(fileName + ".xml");
                        zipEntry.DateTime = DateTime.Now;
                        zipEntry.Size = buffer.Length;
                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(buffer, 0, buffer.Length);
                        zipStream.Finish();

                        res.Clear();
                        res.AddHeader("Content-Type", "binary/octet-stream");
                        res.AddHeader("Content-Length", zipStream.Length.ToString());
                        res.AddHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".zip\"; size=" + zipStream.Length.ToString());
                        res.Flush();

                        res.BinaryWrite(msZip.ToArray());
                        msZip.Close();
                    }
                } else {
                    wb.Export(msXml);
                    res.Clear();
                    res.AddHeader("Content-Type", "binary/octet-stream");
                    res.AddHeader("Content-Length", msXml.Length.ToString());
                    res.AddHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".xml\"; size=" + msXml.Length.ToString()); // todo: include date in filename
                    msXml.WriteTo(res.OutputStream);
                }
                res.Flush();
                res.End();
            }
        } catch(Exception ex) {
            error = ex;
        } finally {
            if(db != null) {
                db.CloseConnection();
            }
        }
        if(error != null) {
            throw error; // after closing the DB connection
        }
    }

    /// <summary>
    /// Exports resources to a ResourceBlender translations database.
    /// </summary>
    /// <param name="log">The delegate to use to log status messages.</param>
    /// <param name="applicationIds">The IDs of the applications to export.</param>
    /// <param name="selectedLanguages">The IDs of the languages to export.</param>
    /// <param name="selectedBundles">The IDs of the bundles to export.</param>
    /// <param name="zipOutput"><c>true</c> if the file should be zipped before sending.</param>
    public static void ExportToRBT(StatusMessage.StatusChangedEventHandler log, string[] applicationIds, string[] selectedLanguages, string[] selectedBundles, bool zipOutput) {
        throw new ResourceBlenderException("Until SQLite works on medium trust, RBT export will be disabled. Please Use ResourceBlender Express to export to an RBT translation database.");
        /*Database db = null;
        Exception error = null;
        if(log == null) {
            log = new StatusMessage.StatusChangedEventHandler(message => {
               // System.Diagnostics.Debug.WriteLine(message.Message);
            });
        }
        try {
            List<RBElement> elements = new List<RBElement>();
            Dictionary<int, RBResource> resources = new Dictionary<int, RBResource>();
            db = new Database();
            db.OpenConnection();

            using(Database.Command cmd = db.CreateCommand()) {
                log(new StatusMessage("Reading resources...", StatusMessage.StatusMessageType.Status));
                cmd.CommandText = string.Format(@"SELECT DISTINCT e.resourceid, r.language, r.translation, r.translatorcomment, c.comment FROM {0}elements AS e 
                                                  INNER JOIN {0}resources AS r ON r.resourceid = e.resourceid LEFT JOIN {0}resourcecomments AS c ON c.resourceid = r.resourceid 
                                                  WHERE {1} {2} {3}",
                                                  db.TablePrefix,
                                                  db.GenerateInClause("applicationid", applicationIds, false),
                                                  db.GenerateInClause("language", selectedLanguages, true),
                                                  db.GenerateInClause("bundle", selectedBundles, true));
                using(DbDataReader reader = cmd.ExecuteReader()) {
                    while(reader.Read()) {
                        try {
                            int resourceId = int.Parse(reader["resourceid"].ToString());
                            string langCode = reader["language"].ToString(),
                                   translation = reader["translation"].ToString(),
                                   translatorComment = reader["translatorcomment"].ToString();
                            RBTranslation translatedResource = new RBTranslation(langCode, translation, translatorComment);
                            if(resources.ContainsKey(resourceId)) {
                                resources[resourceId].Translations.Add(langCode, translatedResource);
                            } else {
                                RBResource resource = new RBResource(resourceId, reader["comment"].ToString());
                                resource.Translations.Add(langCode, translatedResource);
                                resources.Add(resourceId, resource);
                            }

                        } catch(Exception ex) {
                            log(new StatusMessage(string.Format("Error reading resource: {0}", ex.Message), StatusMessage.StatusMessageType.Error));
                        }
                    }
                    reader.Close();
                }

                cmd.CommandText = string.Format(@"SELECT a.displayname, b.name AS bundle, e.elementname, e.resourceid, e.comment FROM {0}applications AS a 
                                                  INNER JOIN {0}elements AS e ON a.id = e.applicationid LEFT JOIN {0}bundles AS b ON e.bundle = b.id WHERE resourceid = @rID", db.TablePrefix);
                cmd.AddParameterWithValue("@rID", "");
                foreach(KeyValuePair<int, RBResource> resource in resources) {
                    cmd.Parameters[0].Value = resource.Value.ID;
                    using(DbDataReader elementReader = cmd.ExecuteReader()) {
                        while(elementReader.Read()) {
                            RBElement element = new RBElement(resource.Value.ID, elementReader["elementname"].ToString(), null, elementReader["bundle"].ToString(), elementReader["comment"].ToString());
                            element.Application = new RBApplication();
                            element.Application.DisplayName = elementReader["displayname"].ToString();
                            elements.Add(element);
                        }
                        elementReader.Close();
                    }
                }
                db.CloseConnection();
            }

            log(new StatusMessage("Exporting resources...", StatusMessage.StatusMessageType.Status));
            int numExported = 0;
            string fileName;
            try {
                fileName = Path.GetTempFileName();
                SQLiteConnection.CreateFile(fileName);
            } catch(Exception ex) {
                log(new StatusMessage(string.Format("Error creating file: {0}", ex.Message), StatusMessage.StatusMessageType.Error));
                throw;
            }
            using(SQLiteConnection fConn = new SQLiteConnection(Database.GetSQLiteConnectionString(fileName))) {
                fConn.Open();

                using(SQLiteTransaction trans = fConn.BeginTransaction()) {
                    using(SQLiteCommand cmdElement = fConn.CreateCommand()) {
                        cmdElement.CommandText = "CREATE TABLE elements (applicationname text(255) NOT NULL, bundle text(10) NOT NULL, elementname text(255), resourceid int(10), comment text, UNIQUE (applicationname,elementname,bundle));";
                        cmdElement.ExecuteNonQuery();

                        cmdElement.CommandText = "INSERT INTO elements (applicationname, bundle, elementname, resourceid, comment) VALUES (@appName, @bundle,  @elementName, @resourceId, @comment);";
                        cmdElement.Parameters.AddWithValue("@appName", "");
                        cmdElement.Parameters.AddWithValue("@bundle", "");
                        cmdElement.Parameters.AddWithValue("@elementName", "");
                        cmdElement.Parameters.AddWithValue("@resourceId", "");
                        cmdElement.Parameters.AddWithValue("@comment", "");

                        foreach(RBElement element in elements) {
                            try {
                                cmdElement.Parameters["@appName"].Value = element.Application.DisplayName;
                                cmdElement.Parameters["@bundle"].Value = element.BundleName;
                                cmdElement.Parameters["@elementName"].Value = element.Name;
                                cmdElement.Parameters["@resourceId"].Value = element.ID;
                                cmdElement.Parameters["@comment"].Value = element.Comment;
                                cmdElement.ExecuteNonQuery();
                                log(new StatusMessage(string.Format("Exported element '{0}'", element.Name), StatusMessage.StatusMessageType.Success));
                            } catch(Exception ex) {
                                log(new StatusMessage(string.Format("Error saving element: {0}", ex.Message), StatusMessage.StatusMessageType.Error));
                            }
                        }
                    }
                    using(SQLiteCommand cmdResource = fConn.CreateCommand())
                    using(SQLiteCommand cmdResourceComment = fConn.CreateCommand()) {
                        // if there are no resources for a particular language, a SELECT DISTINCT will fail, so store the languages in a separate table
                        cmdResource.CommandText = "CREATE TABLE languages (language text(10) PRIMARY KEY NOT NULL);";
                        cmdResource.ExecuteNonQuery();
                        cmdResource.CommandText = "INSERT INTO languages (language) VALUES (@language);";
                        cmdResource.Parameters.AddWithValue("@language", "");
                        foreach(string langCode in selectedLanguages) {
                            cmdResource.Parameters[0].Value = langCode;
                            cmdResource.ExecuteNonQuery();
                        }
                        cmdResource.Parameters.Clear();

                        cmdResource.CommandText = "CREATE TABLE resources (resourceid int(10) NOT NULL, language text(10) NOT NULL, translation text NOT NULL, translatorcomment text, PRIMARY KEY (resourceid,language));";
                        cmdResource.ExecuteNonQuery();
                        cmdResourceComment.CommandText = "CREATE TABLE resourcecomments (resourceid int(10) PRIMARY KEY NOT NULL, comment text);";
                        cmdResourceComment.ExecuteNonQuery();

                        cmdResource.CommandText = "INSERT INTO resources (resourceid, language, translation, translatorcomment) VALUES (@resourceid, @language, @translation, @translatorcomment);";
                        cmdResource.Parameters.AddWithValue("@resourceid", "");
                        cmdResource.Parameters.AddWithValue("@language", "");
                        cmdResource.Parameters.AddWithValue("@translation", "");
                        cmdResource.Parameters.AddWithValue("@translatorcomment", "");

                        cmdResourceComment.CommandText = "INSERT INTO resourcecomments (resourceid, comment) VALUES (@resourceId, @comment);";
                        cmdResourceComment.Parameters.AddWithValue("@resourceid", "");
                        cmdResourceComment.Parameters.AddWithValue("@comment", "");

                        foreach(KeyValuePair<int, RBResource> resource in resources) {
                            try {
                                cmdResource.Parameters["@resourceid"].Value = resource.Value.ID;
                                foreach(KeyValuePair<string, RBTranslation> translation in resource.Value.Translations) {
                                    cmdResource.Parameters["@language"].Value = translation.Value.LanguageCode;
                                    cmdResource.Parameters["@translation"].Value = translation.Value.Translation;
                                    cmdResource.Parameters["@translatorcomment"].Value = translation.Value.Comment;
                                    cmdResource.ExecuteNonQuery();
                                }
                                if(resource.Value.Comment.Length > 0) {
                                    cmdResourceComment.Parameters["@resourceid"].Value = resource.Value.ID;
                                    cmdResourceComment.Parameters["@comment"].Value = resource.Value.Comment;
                                    cmdResourceComment.ExecuteNonQuery();
                                }
                                numExported++;
                                log(new StatusMessage(string.Format("Exported resource #{0}", resource.Value.ID), StatusMessage.StatusMessageType.Success));
                            } catch(Exception ex) {
                                log(new StatusMessage(string.Format("Error saving resource: {0}", ex.Message), StatusMessage.StatusMessageType.Error));
                            }
                        }
                    }
                    trans.Commit();
                }
            }
            //fConn.Close();

            FileStream fStream = File.OpenRead(fileName);
            byte[] buffer = new byte[Convert.ToInt32(fStream.Length)];
            fStream.Read(buffer, 0, (int)fStream.Length);
            fStream.Close();
            try {
                if(zipOutput) {
                    using(MemoryStream msZip = new MemoryStream()) { // output stream for zip
                        ZipOutputStream zipStream = new ZipOutputStream(msZip);
                        zipStream.SetLevel(9);

                        ZipEntry zipEntry = new ZipEntry("ResourceBlender-" + DateTime.Now.ToString("yyyy-MM-dd") + ".rbt");
                        zipEntry.DateTime = DateTime.Now;
                        zipEntry.Size = buffer.Length;
                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(buffer, 0, buffer.Length);
                        zipStream.Finish();
                        SendZip(msZip, zipStream, "ResourceBlender");
                        zipStream.Close();
                    }
                } else {
                    HttpResponse res = HttpContext.Current.Response;
                    res.Clear();
                    res.AddHeader("Content-Type", "binary/octet-stream");
                    res.AddHeader("Content-Length", buffer.Length.ToString());
                    res.AddHeader("Content-Disposition", "attachment; filename=\"" + "ResourceBlender-" + DateTime.Now.ToString("yyyy-MM-dd") + ".rbt\"; size=" + buffer.Length.ToString());
                    res.Flush();

                    res.BinaryWrite(buffer);
                    res.Flush();
                    res.End();

                }
            } catch(Exception e) {
                System.Diagnostics.Debug.WriteLine(e.Message);
            }
            log(new StatusMessage(string.Format("{0} resources exported.", numExported), StatusMessage.StatusMessageType.Status));
        } catch(Exception ex) {
            error = ex;
        } finally {
            if(db != null) {
                db.CloseConnection();
            }
        }
        if(error != null) {
            log(new StatusMessage(string.Format("Error: {0}", error.Message), StatusMessage.StatusMessageType.Error));
            throw error; // after closing the DB connection
        }*/
    }


    /// <summary>
    /// Sends a zip file to the browse.
    /// </summary>
    /// <param name="zipStream">The zip stream.</param>
    /// <param name="fileName">Name of the file.</param>
    public static void SendZip(MemoryStream ms, ZipOutputStream zipStream, string fileName) {
        zipStream.Finish();
        HttpResponse res = HttpContext.Current.Response;
        res.Clear();
        res.AddHeader("Content-Type", "binary/octet-stream");
        res.AddHeader("Content-Length", zipStream.Length.ToString());
        string outputFileName = string.Format("{0}-{1}.zip", fileName, DateTime.Now.ToString("yyyy-MM-dd"));
        res.AddHeader("Content-Disposition", "attachment; filename=\"" + outputFileName + "\"; size=" + zipStream.Length.ToString());
        res.Flush();

        res.BinaryWrite(ms.ToArray());
        res.Flush();
        res.End();

        ms.Close();
        ms.Dispose();
    }


}
